package com.aizuda.boot.modules.business.counterparty.service.impl;

import cn.hutool.core.thread.ThreadUtil;
import com.aizuda.boot.modules.business.contract.dto.SingleResponse;
import com.aizuda.boot.modules.business.contract.dto.UserInfo;
import com.aizuda.boot.modules.business.contract.entity.ContractFieldValueEntity;
import com.aizuda.boot.modules.business.contract.mapper.ContractFieldValueMapper;
import com.aizuda.boot.modules.business.counterparty.entity.*;
import com.aizuda.boot.modules.common.constant.enums.*;
import com.aizuda.boot.modules.business.contract.util.AuthUtil;
import com.aizuda.boot.modules.business.counterparty.entity.dto.*;
import com.aizuda.boot.modules.business.counterparty.entity.vo.ChangeRecordVO;
import com.aizuda.boot.modules.business.counterparty.entity.vo.CreateBankInfoRespDTO;
import com.aizuda.boot.modules.business.counterparty.entity.vo.LegalEntityPageRespDTO;
import com.aizuda.boot.modules.business.counterparty.entity.vo.LegalVO;
import com.aizuda.boot.modules.business.counterparty.mapper.BankInfoMapper;
import com.aizuda.boot.modules.business.counterparty.mapper.LegalEntityDetailMapper;
import com.aizuda.boot.modules.business.counterparty.mapper.LegalTenantRelMapper;
import com.aizuda.boot.modules.business.counterparty.service.BankInfoService;
import com.aizuda.boot.modules.business.counterparty.service.LegalEntityService;
import com.aizuda.boot.modules.business.counterparty.service.ChangeRecordService;
import com.aizuda.boot.modules.file.service.FileInfoService;
import com.aizuda.boot.modules.business.dict.service.CountryDictService;
import com.aizuda.boot.modules.file.util.FileResponseUtil;
import com.aizuda.boot.modules.system.entity.SysDepartment;
import com.aizuda.boot.modules.system.mapper.SysDepartmentMapper;
import com.aizuda.core.api.ApiAssert;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import jakarta.annotation.Resource;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 法人实体服务实现类
 */
@Slf4j
@Service
public class LegalEntityServiceImpl extends ServiceImpl<LegalEntityDetailMapper, LegalEntityDetailEntity> implements LegalEntityService {

    @Resource
    private LegalEntityDetailMapper legalEntityDetailMapper;

    @Resource
    private BankInfoMapper bankInfoMapper;

    @Resource
    private CountryDictService countryDictService;

    @Resource
    private BankInfoService bankInfoService;

    @Resource
    private ChangeRecordService changeRecordService;

    @Resource
    private FileInfoService fileInfoService;

    @Resource
    private LegalTenantRelMapper legalTenantRelMapper;

    @Resource
    private SysDepartmentMapper sysDepartmentMapper;

    @Resource
    private ContractFieldValueMapper contractFieldValueMapper;

    @Override
    public List<LegalEntityDetailEntity> getEnabledLegalEntities() {
        return legalEntityDetailMapper.selectList(
                new LambdaQueryWrapper<LegalEntityDetailEntity>()
                        .eq(LegalEntityDetailEntity::getIsEnabled, true)
                        .orderByAsc(LegalEntityDetailEntity::getLegalEntityCode)
        );
    }

    @Override
    public LegalEntityDetailEntity getLegalEntityById(Integer id) {
        return legalEntityDetailMapper.selectById(id);
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean toggleLegalEntityStatus(Integer id, Boolean enabled) {
        LegalEntityDetailEntity entity = this.getById(id);
        if (null == entity) {
            ApiAssert.fail("要更新的法人实体不存在");
        }
        if (!Objects.equals(entity.getIsEnabled(), enabled)) {
            UserInfo currentUser = AuthUtil.getCurrentUser();
            entity.setIsEnabled(enabled);
            entity.setUpdateBy(currentUser.getNickName());
            entity.setUpdatedAt(LocalDateTime.now());

            int result = legalEntityDetailMapper.updateById(entity);

            updateStatusRecord(id.longValue(), enabled, currentUser.getId(), currentUser.getNickName());

            if (result > 0) {
                log.info("法人实体状态更新成功，ID: {}, 状态: {}", id, enabled ? "启用" : "停用");
                return true;
            } else {
                return false;
            }
        }

        return true;
    }

    private void updateStatusRecord(long id, Boolean enabled, Long userId, String username) {
        Integer currentMaxVersion = getCurrentMaxVersion(id);
        int newVersion = currentMaxVersion == null ? 1 : currentMaxVersion + 1;
        ChangeRecord changeRecord = new ChangeRecord();
        changeRecord.setLegalId(id);
        changeRecord.setField("isEnabled");
        changeRecord.setOldValue(enabled ? "0" : "1");
        changeRecord.setNewValue(enabled ? "1" : "0");
        changeRecord.setVersion(newVersion);
        changeRecord.setCreateId(userId);
        changeRecord.setCreateBy(username);
        changeRecord.setCreateTime(new Date());
        changeRecordService.save(changeRecord);
    }


    @Override
    public LegalEntityPageRespDTO queryLegalEntitiesPage(LegalEntityQueryReqDTO reqDTO) {
        // 构建分页对象
        Page<LegalEntityDetailEntity> page = new Page<>(reqDTO.getPageNum(), reqDTO.getPageSize());

        // 构建查询条件
        LambdaQueryWrapper<LegalEntityDetailEntity> queryWrapper = new LambdaQueryWrapper<>();

        // 模糊匹配法人实体名称
        if (StringUtils.hasText(reqDTO.getKeyword())) {
            queryWrapper.like(LegalEntityDetailEntity::getLegalEntityName, reqDTO.getKeyword())
                    .or()
                    .like(LegalEntityDetailEntity::getLegalEntityAbbr, reqDTO.getKeyword())
                    .or()
                    .like(LegalEntityDetailEntity::getLegalEntityCode, reqDTO.getKeyword());
        }
        if (!CollectionUtils.isEmpty(reqDTO.getTaxpayer())) {
            queryWrapper.in(LegalEntityDetailEntity::getTaxpayer, reqDTO.getTaxpayer());
        }
        if (!CollectionUtils.isEmpty(reqDTO.getRegisterCountry())) {
            queryWrapper.in(LegalEntityDetailEntity::getRegisterCountry, reqDTO.getRegisterCountry());
        }
        if (null != reqDTO.getIsEnabled()) {
            queryWrapper.eq(LegalEntityDetailEntity::getIsEnabled, reqDTO.getIsEnabled());
        }

        // 租户过滤
        if (StringUtils.hasText(reqDTO.getSysTenantKey())) {
            List<Integer> list = legalTenantRelMapper.selectList(Wrappers.<LegalTenantRel>lambdaQuery()
                            .eq(LegalTenantRel::getSysTenantKey, reqDTO.getSysTenantKey())
                            .select(LegalTenantRel::getLegalId))
                    .stream().map(LegalTenantRel::getLegalId).toList();
            if (!CollectionUtils.isEmpty(list)) {
                queryWrapper.in(LegalEntityDetailEntity::getId, list);
            } else {
                // 如果没有找到关联记录，返回空的分页结果
                LegalEntityPageRespDTO respDTO = new LegalEntityPageRespDTO();
                respDTO.setTotal(0L);
                respDTO.setPageNum(reqDTO.getPageNum());
                respDTO.setPageSize(reqDTO.getPageSize());
                respDTO.setTotalPages(0);
                respDTO.setItems(new ArrayList<>());
                return respDTO;
            }
        }

        // 按创建时间倒序排序
        queryWrapper.orderByDesc(LegalEntityDetailEntity::getCreatedAt);

        // 执行分页查询
        IPage<LegalEntityDetailEntity> pageResult = legalEntityDetailMapper.selectPage(page, queryWrapper);

        // 构建响应对象
        LegalEntityPageRespDTO respDTO = new LegalEntityPageRespDTO();
        respDTO.setTotal(pageResult.getTotal());
        respDTO.setPageNum(reqDTO.getPageNum());
        respDTO.setPageSize(reqDTO.getPageSize());
        respDTO.setTotalPages((int) pageResult.getPages());

        // 转换实体列表
        List<LegalEntityPageRespDTO.LegalEntityItemDTO> items = pageResult.getRecords().stream()
                .map(this::convertToItemDTO)
                .collect(Collectors.toList());

        respDTO.setItems(items);

        return respDTO;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean saveLegal(CreateLegalInfoDTO dto, HttpServletRequest request) {
        log.info("开始创建法人实体，请求参数：{}", dto);
        if (null == dto || !StringUtils.hasText(dto.getLegalEntityName()) || !StringUtils.hasText(dto.getLegalEntityAbbr())){
            ApiAssert.fail("请填写完整的法人信息");
        }

        // 验证租户信息
        if (CollectionUtils.isEmpty(dto.getSysTenantKey())) {
            ApiAssert.fail("租户不能为空，请选择法人实体适用的租户");
        }
        if (dto.getSysTenantKey().size() > 1) {
            ApiAssert.fail("请选择一个租户");
        }
        int count = legalEntityDetailMapper.checkCounterparty(null, dto.getLegalEntityAbbr(), dto.getCertificateId(), dto.getSysTenantKey().get(0));
        if (count > 0) {
            ApiAssert.fail("法人已经存在");
        }

        // 判断客户端类型
        String clientType = determineClientType(request);
        log.info("创建法人实体 - 客户端类型: {}", clientType);

        // 创建法人实体对象
        LegalEntityDetailEntity entity = new LegalEntityDetailEntity();

        // 设置基本信息
        entity.setLegalEntityCode(generateLegalEntityCode());
        entity.setLegalEntityName(dto.getLegalEntityName());
        entity.setLegalEntityAbbr(dto.getLegalEntityAbbr());
        entity.setRegisterCountry(dto.getRegisterCountry());
        entity.setDetailAddress(dto.getDetailAddress());
        entity.setCertificateType(dto.getCertificateType());
        entity.setCertificateId(dto.getCertificateId());

        // 获取登录用户信息
        UserInfo currentUser = AuthUtil.getCurrentUser();

        // 设置默认值
        entity.setIsEnabled(true);
        entity.setCreateAppid(clientType);
        entity.setCreateSource(clientType);
        entity.setUpdateAppid(clientType);
        entity.setUpdateSource(clientType);
        entity.setCreateId(currentUser.getId());
        entity.setCreateBy(currentUser.getNickName());
        entity.setCreatedAt(LocalDateTime.now());
        entity.setUpdateBy(currentUser.getNickName());
        entity.setUpdatedAt(LocalDateTime.now());
        entity.setDeleted(false);

        if (!CollectionUtils.isEmpty(dto.getAttachments())) {
            entity.setAttachment(dto.getAttachments().stream().map(String::valueOf).collect(Collectors.joining(",")));
        }

        // 保存法人实体到数据库
        int result = legalEntityDetailMapper.insert(entity);
        if (result <= 0) {
            throw new RuntimeException("法人实体创建失败");
        }

        log.info("法人实体创建成功，ID: {}, 名称: {}", entity.getId(), entity.getLegalEntityName());

        // 创建法人实体与租户的关联关系
        bindingDepartment(entity.getId(),dto.getSysTenantKey());

        // 创建银行信息并绑定到法人实体
        createAndBindBankInfosForLegal(dto.getBankInfoList(), entity.getId());

        return true;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean updateLegal(UpdateLegalInfoDTO dto, HttpServletRequest request) {
        log.info("开始更新法人实体，请求参数：{}", dto);
        if (null == dto || null == dto.getId()) {
            ApiAssert.fail("参数错误");
        }
        int count = legalEntityDetailMapper.checkCounterparty(dto.getId(), dto.getLegalEntityAbbr(), dto.getCertificateId(), dto.getSysTenantKey().get(0));
        if (count > 0) {
            ApiAssert.fail("法人已经存在");
        }

        // 验证租户信息
        if (CollectionUtils.isEmpty(dto.getSysTenantKey())) {
            ApiAssert.fail("租户不能为空，请选择法人实体适用的租户");
        }
        if (dto.getSysTenantKey().size() > 1) {
            ApiAssert.fail("请选择一个租户");
        }

        // 验证法人实体是否存在
        LegalEntityDetailEntity existingEntity = legalEntityDetailMapper.selectById(dto.getId());
        if (existingEntity == null) {
            ApiAssert.fail("法人实体不存在");
        }

        // 判断客户端类型
        String clientType = determineClientType(request);
        log.info("更新法人实体 - 客户端类型: {}", clientType);

        // 获取登录用户信息
        UserInfo currentUser = AuthUtil.getCurrentUser();

        // 更新法人实体基本信息
        LegalEntityDetailEntity updateEntity = new LegalEntityDetailEntity();
        updateEntity.setId(dto.getId());
        updateEntity.setLegalEntityName(dto.getLegalEntityName());
        updateEntity.setLegalEntityAbbr(dto.getLegalEntityAbbr());
        updateEntity.setRegisterCountry(dto.getRegisterCountry());
        updateEntity.setDetailAddress(dto.getDetailAddress());
        updateEntity.setCertificateType(dto.getCertificateType());
        updateEntity.setCertificateId(dto.getCertificateId());

        // 设置更新审计字段
        updateEntity.setUpdateAppid(clientType);
        updateEntity.setUpdateSource(clientType);
        updateEntity.setUpdateBy(currentUser.getNickName());
        updateEntity.setUpdatedAt(LocalDateTime.now());

        if (!CollectionUtils.isEmpty(dto.getAttachments())) {
            updateEntity.setAttachment(dto.getAttachments().stream().map(String::valueOf).collect(Collectors.joining(",")));
        } else {
            updateEntity.setAttachment(null);
        }

        String bankInfoIds = existingEntity.getBankInfoIds();

        updateEntity.setBankInfoIds(null);

        // 比较字段变化并保存修改记录
        compareAndSaveChangeRecordByDto(existingEntity, dto, dto.getId().longValue(), currentUser.getId(), currentUser.getNickName());

        // 更新法人实体到数据库
        boolean result = this.updateById(updateEntity);
        if (!result) {
            throw new RuntimeException("法人实体更新失败，ID: " + dto.getId());
        }
        log.info("法人实体更新成功，ID: {}, 名称: {}", dto.getId(), dto.getLegalEntityName());

        // 更新租户关联关系
        // 先删除原有的租户关联
        legalTenantRelMapper.delete(new LambdaQueryWrapper<LegalTenantRel>()
                .eq(LegalTenantRel::getLegalId, dto.getId()));

        // 创建新的租户关联
        bindingDepartment(dto.getId(),dto.getSysTenantKey());

        // 更新银行信息
        if (StringUtils.hasText(bankInfoIds)) {
            List<Integer> bankIds = Arrays.stream(bankInfoIds.split(",")).map(Integer::parseInt).collect(Collectors.toList());
            if (!CollectionUtils.isEmpty(bankIds)) {
                bankInfoMapper.deleteByIds(bankIds);
            }
        }
        createAndBindBankInfosForLegal(dto.getBankInfoList(), updateEntity.getId());


        return true;
    }

    @Override
    public List<ChangeRecordVO> queryChangeRecords(Integer legalId) {
        LegalEntityDetailEntity entity = getById(legalId);
        if (null == entity) {
            return new ArrayList<>();
        }
        return changeRecordService.queryChangeRecords(legalId, "legal");
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public SingleResponse<Boolean> uploadCounterparty(MultipartFile file) {
        if (null == file || file.isEmpty()) {
            return SingleResponse.failure(ResponseCodeEnum.UPLOAD_FILE_ERROR.getCode(), ResponseCodeEnum.UPLOAD_FILE_ERROR.getMsg());
        }
        String originalFilename = file.getOriginalFilename();
        if (originalFilename == null || (!originalFilename.endsWith(".xlsx") && !originalFilename.endsWith(".xls"))) {
            return SingleResponse.failure(ResponseCodeEnum.UPLOAD_FILE_ERROR.getCode(), ResponseCodeEnum.UPLOAD_FILE_ERROR.getMsg());
        }
        try (XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream())){
            XSSFSheet infoSheet = workbook.getSheetAt(0);
            XSSFSheet bankSheet = workbook.getSheetAt(2);
            if (infoSheet.getLastRowNum() < 2 && bankSheet.getLastRowNum() < 2){
                return SingleResponse.failure(ResponseCodeEnum.FILE_EMPTY.getCode(), ResponseCodeEnum.FILE_EMPTY.getMsg());
            }
            // 验证交易方信息表头
            validateLegalHeaders(infoSheet);

            // 验证银行信息表头
            validateBankHeaders(bankSheet);
        }catch (Exception e){
            log.error("解析Excel文件失败：{}", e.getMessage());
            return SingleResponse.failure(ResponseCodeEnum.UPLOAD_FILE_ERROR.getCode(), ResponseCodeEnum.UPLOAD_FILE_ERROR.getMsg());
        }
        log.info("开始处理法人批量上传文件：{}", originalFilename);
        try {
            List<UploadLegalDTO> legalDataList = parseLegalExcelFile(file);
            List<UploadLegalBankDTO> legalBankDataList = parseLegalBankExcelFile(file);

            UserInfo currentUser = AuthUtil.getCurrentUser();

            ThreadUtil.execute(new Runnable() {
                @Override
                public void run() {
                    Map<ChangeTypeEnum, List<UploadLegalDTO>> legalMap = legalDataList.stream().collect(Collectors.groupingBy(UploadLegalDTO::getType));
                    Map<ChangeTypeEnum, List<UploadLegalBankDTO>> legalBankMap = legalBankDataList.stream().collect(Collectors.groupingBy(UploadLegalBankDTO::getType));

                    Map<Long, Integer> serialToCounterpartyIdMap = new HashMap<>();

                    Map<Long, Integer> counterpartyVersionMap = preAllocateVersions(legalDataList, legalBankDataList);

                    // 处理法人数据
                    processLegalData(legalMap, currentUser, serialToCounterpartyIdMap, counterpartyVersionMap);

                    // 处理法人银行数据
                    processBankData(legalBankMap, currentUser, serialToCounterpartyIdMap, counterpartyVersionMap);

                    // 处理租户关联
                    processLegalTenantRel(serialToCounterpartyIdMap, currentUser);

                    log.info("法人批量上传处理完成，法人数据{}条，银行数据{}条", legalDataList.size(), legalBankDataList.size());
                }
            });

            return SingleResponse.of(true);
        }catch (Exception e){
            log.error("解析Excel文件失败：{}", e.getMessage(), e);
            throw new RuntimeException(e);
        }
    }

    private void processLegalTenantRel(Map<Long, Integer> serialToCounterpartyIdMap, UserInfo currentUser) {
        String tenantKey = currentUser.getSysTenantVo().getTenantKey();

        // 1. 获取所有交易方ID
        List<Integer> legalIds = serialToCounterpartyIdMap.values().stream()
                .distinct()
                .toList();

        // 2. 先删除原有租户关系
        if (!legalIds.isEmpty()) {
            legalTenantRelMapper.delete(Wrappers.<LegalTenantRel>lambdaQuery()
                    .in(LegalTenantRel::getLegalId, legalIds)
                    .eq(LegalTenantRel::getSysTenantKey, tenantKey));
        }

        // 3. 创建并插入新记录
        List<LegalTenantRel> list = legalIds.stream()
                .map(legalId -> {
                    LegalTenantRel rel = new LegalTenantRel();
                    rel.setLegalId(legalId);
                    rel.setSysTenantKey(tenantKey);
                    rel.setCreateId(currentUser.getId());
                    rel.setCreateBy(currentUser.getNickName());
                    rel.setCreatedAt(new Date());
                    rel.setDeleted(false);
                    return rel;
                })
                .collect(Collectors.toList());

        if (!list.isEmpty()) {
            legalTenantRelMapper.insert(list);
        }
    }

    @Override
    public List<LegalVO> getLegalEntityList() {
        return list(Wrappers.<LegalEntityDetailEntity>lambdaQuery()
                .eq(LegalEntityDetailEntity::getIsEnabled, true))
                .stream().map(item -> {
                    LegalVO vo = new LegalVO();
                    vo.setId(item.getId());
                    vo.setLegalEntityCode(item.getLegalEntityCode());
                    vo.setLegalEntityName(item.getLegalEntityName());
                    return vo;
            }).collect(Collectors.toList());
    }

    @Override
    public Boolean deleteLegal(Integer legalId) {
        if (null == legalId) {
            return false;
        }
        LegalEntityDetailEntity legal = this.getById(legalId);
        if (null == legal) {
            return false;
        }
        List<ContractFieldValueEntity> contractFieldValueEntities = contractFieldValueMapper.selectList(Wrappers.<ContractFieldValueEntity>lambdaQuery()
                .eq(ContractFieldValueEntity::getFieldCode, SystemFieldsEnum.OUR.getCode()));
        contractFieldValueEntities.forEach(item -> {
            String fieldValue = item.getFieldValue();
            JSONArray array = JSON.parseArray(fieldValue);
            if (array.contains(legalId)){
                ApiAssert.fail("法人实体已经被关联，不允许删除！");
            }
        });
        // 删除法人租户关联
        legalTenantRelMapper.delete(new LambdaQueryWrapper<LegalTenantRel>()
                .eq(LegalTenantRel::getLegalId, legalId));
        // 删除银行关联
        String bankInfoIds = legal.getBankInfoIds();
        if (StringUtils.hasText(bankInfoIds)) {
            List<String> bankIds = Arrays.stream(bankInfoIds.split(",")).toList();
            if (!CollectionUtils.isEmpty(bankIds)) {
                bankInfoMapper.deleteByIds(bankIds);
            }
        }
        return this.removeById(legalId);

    }

    @Override
    public void exportLegalEntity(ExportDTO dto, HttpServletResponse response) {
        if (null == dto || CollectionUtils.isEmpty(dto.getIds())) {
            FileResponseUtil.write(response, SingleResponse.failure(ResponseCodeEnum.PARAM_BINDING_EXCEPTION.getCode(), ResponseCodeEnum.PARAM_BINDING_EXCEPTION.getMsg()));
            return;
        }
        // 查询要导出的法人数据
        List<LegalEntityDetailEntity> legalEntityDetailEntities = this.lambdaQuery()
                .in(LegalEntityDetailEntity::getId, dto.getIds())
                .list();
        if (CollectionUtils.isEmpty(legalEntityDetailEntities)){
            log.info("查询不到法人数据");
            FileResponseUtil.write(response, SingleResponse.failure(ResponseCodeEnum.DATA_NOT_FOUND.getCode(), ResponseCodeEnum.DATA_NOT_FOUND.getMsg()));
            return;
        }

        HashMap<Integer, Integer> bankIdToCounterpartyId = new HashMap<>();
        Set<Integer> bankIds = new HashSet<>();
        for (LegalEntityDetailEntity entity : legalEntityDetailEntities) {
            if (StringUtils.hasText(entity.getBankInfoIds())) {
                String[] idArr = entity.getBankInfoIds().split(",");
                for (String idStr : idArr) {
                    try {
                        Integer bankId = Integer.parseInt(idStr.trim());
                        bankIds.add(bankId);
                        bankIdToCounterpartyId.put(bankId, entity.getId());
                    } catch (NumberFormatException ignored) {
                    }
                }
            }
        }
        List<BankInfoEntity> bankInfos = new ArrayList<>();
        if (!bankIds.isEmpty()) {
            bankInfos = bankInfoMapper.selectByIds(new ArrayList<>(bankIds));
        }

        // 为导出建立legalId -> LegalEntity映射，及序号映射
        Map<Integer, LegalEntityDetailEntity> idToCounterparty = legalEntityDetailEntities.stream()
                .collect(Collectors.toMap(LegalEntityDetailEntity::getId, e -> e));
        Map<Integer, Integer> counterpartyIdToSerial = new HashMap<>();
        for (int i = 0; i < legalEntityDetailEntities.size(); i++) {
            counterpartyIdToSerial.put(legalEntityDetailEntities.get(i).getId(), i + 1);
        }

        // 创建Excel并写入
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            // 样式与字体
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerFont.setFontHeightInPoints((short) 14);

            Font dataFont = workbook.createFont();
            dataFont.setFontHeightInPoints((short) 12);

            CellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFont(headerFont);
            headerStyle.setAlignment(HorizontalAlignment.CENTER);
            headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            CellStyle dataStyle = workbook.createCellStyle();
            dataStyle.setFont(dataFont);
            dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            // 法人信息sheet
            XSSFSheet legalSheet = workbook.createSheet("法人信息");
            legalSheet.setDefaultRowHeightInPoints(20f);
            String[] legalHeaders = {"基础信息序号", "法人实体编码", "法人实体名称", "法人实体简称", "注册国家/地区", "详细地址", "证件类型", "证件ID"};
            Row legalHeaderRow = legalSheet.createRow(0);
            legalHeaderRow.setHeightInPoints(24f);
            for (int i = 0; i < legalHeaders.length; i++) {
                Cell cell = legalHeaderRow.createCell(i);
                cell.setCellValue(legalHeaders[i]);
                cell.setCellStyle(headerStyle);
            }

            for (int i = 0; i < legalEntityDetailEntities.size(); i++) {
                LegalEntityDetailEntity e = legalEntityDetailEntities.get(i);
                Row row = legalSheet.createRow(i + 1);
                row.setHeightInPoints(20f);
                int col = 0;
                Cell c0 = row.createCell(col++);
                c0.setCellValue(i + 1);
                c0.setCellStyle(dataStyle);
                Cell c1 = row.createCell(col++);
                c1.setCellValue(e.getLegalEntityCode() == null ? "" : e.getLegalEntityCode());
                c1.setCellStyle(dataStyle);
                Cell c2 = row.createCell(col++);
                c2.setCellValue(e.getLegalEntityName() == null ? "" : e.getLegalEntityName());
                c2.setCellStyle(dataStyle);
                Cell c3 = row.createCell(col++);
                c3.setCellValue(e.getLegalEntityAbbr() == null ? "" : e.getLegalEntityAbbr());
                c3.setCellStyle(dataStyle);
                Cell c4 = row.createCell(col++);
                String countryName = CountryEnum.getCountryName(e.getRegisterCountry());
                c4.setCellValue(countryName == null ? "" : countryName);
                c4.setCellStyle(dataStyle);
                Cell c5 = row.createCell(col++);
                c5.setCellValue(e.getDetailAddress() == null ? "" : e.getDetailAddress());
                c5.setCellStyle(dataStyle);
                Cell c6 = row.createCell(col++);
                String certTypeDesc = convertCertificateTypeToDesc(e.getCertificateType());
                c6.setCellValue(certTypeDesc == null ? "" : certTypeDesc);
                c6.setCellStyle(dataStyle);
                Cell c7 = row.createCell(col++);
                c7.setCellValue(e.getCertificateId() == null ? "" : e.getCertificateId());
                c7.setCellStyle(dataStyle);
            }

            // 银行信息sheet
            XSSFSheet bankSheet = workbook.createSheet("银行信息");
            bankSheet.setDefaultRowHeightInPoints(20f);
            String[] bankHeaders = {"账户信息 ID", "基础信息序号", "法人实体编码", "账号", "账户名", "银行内部ID", "银行名称", "银行国家", "币种", "总账科目", "清算科目", "账户属性描述"};
            Row bankHeaderRow = bankSheet.createRow(0);
            bankHeaderRow.setHeightInPoints(24f);
            for (int i = 0; i < bankHeaders.length; i++) {
                Cell cell = bankHeaderRow.createCell(i);
                cell.setCellValue(bankHeaders[i]);
                cell.setCellStyle(headerStyle);
            }

            int bankRowIdx = 1;
            for (BankInfoEntity bank : bankInfos) {
                Integer legalId = bankIdToCounterpartyId.get(bank.getId());
                LegalEntityDetailEntity legal = legalId != null ? idToCounterparty.get(legalId) : null;
                if (legal == null) {
                    continue;
                }
                Row row = bankSheet.createRow(bankRowIdx++);
                row.setHeightInPoints(20f);
                int col = 0;
                Cell b0 = row.createCell(col++);
                b0.setCellValue(bank.getId() == null ? "" : String.valueOf(bank.getId()));
                b0.setCellStyle(dataStyle);
                Cell b1 = row.createCell(col++);
                b1.setCellValue(counterpartyIdToSerial.getOrDefault(legal.getId(), 0));
                b1.setCellStyle(dataStyle);
                Cell b2 = row.createCell(col++);
                b2.setCellValue(legal.getLegalEntityCode() == null ? "" : legal.getLegalEntityCode());
                b2.setCellStyle(dataStyle);
                Cell b3 = row.createCell(col++);
                b3.setCellValue(bank.getAccountNumber() == null ? "" : bank.getAccountNumber());
                b3.setCellStyle(dataStyle);
                Cell b4 = row.createCell(col++);
                b4.setCellValue(bank.getAccountName() == null ? "" : bank.getAccountName());
                b4.setCellStyle(dataStyle);
                Cell b5 = row.createCell(col++);
                b5.setCellValue(bank.getBankInternalId() == null ? "" : bank.getBankInternalId());
                b5.setCellStyle(dataStyle);
                Cell b6 = row.createCell(col++);
                b6.setCellValue(bank.getBankName() == null ? "" : bank.getBankName());
                b6.setCellStyle(dataStyle);
                Cell b7 = row.createCell(col++);
                String bankCountryName = CountryEnum.getCountryName(bank.getBankCountry());
                b7.setCellValue(bankCountryName == null ? "" : bankCountryName);
                b7.setCellStyle(dataStyle);
                Cell b8 = row.createCell(col++);
                b8.setCellValue(bank.getCurrency() == null ? "" : bank.getCurrency());
                b8.setCellStyle(dataStyle);
                Cell b9 = row.createCell(col++);
                b9.setCellValue(bank.getGeneralLedgerAccount() == null ? "" : bank.getGeneralLedgerAccount());
                b9.setCellStyle(dataStyle);
                Cell b10 = row.createCell(col++);
                b10.setCellValue(bank.getClearingAccount() == null ? "" : bank.getClearingAccount());
                b10.setCellStyle(dataStyle);
                Cell b11 = row.createCell(col);
                b11.setCellValue(bank.getAccountAttributeDesc() == null ? "" : bank.getAccountAttributeDesc());
                b11.setCellStyle(dataStyle);
            }

            // 自动列宽并适当放大列宽
            for (int i = 0; i < 8; i++) {
                legalSheet.autoSizeColumn(i);
                int width = legalSheet.getColumnWidth(i);
                int minWidth = 20 * 256;
                int target = Math.max(width, minWidth);
                target = Math.min(target + 1024, 255 * 256);
                legalSheet.setColumnWidth(i, target);
            }
            for (int i = 0; i < 12; i++) {
                bankSheet.autoSizeColumn(i);
                int width = bankSheet.getColumnWidth(i);
                int minWidth = 18 * 256;
                int target = Math.max(width, minWidth);
                target = Math.min(target + 1024, 255 * 256);
                bankSheet.setColumnWidth(i, target);
            }

            // 输出
            String fileName = "法人实体数据导出.xlsx";
            try (java.io.ByteArrayOutputStream bos = new java.io.ByteArrayOutputStream()) {
                workbook.write(bos);
                bos.flush();
                byte[] bytes = bos.toByteArray();
                FileResponseUtil.setDownloadFileHeader(response, fileName, (long) bytes.length);
                response.getOutputStream().write(bytes);
                response.flushBuffer();
            }
        } catch (Exception e) {
            log.error("导出法人实体失败", e);
            FileResponseUtil.write(response, SingleResponse.failure(ResponseCodeEnum.ERROR.getCode(), ResponseCodeEnum.ERROR.getMsg()));
        }
    }

    private void processBankData(Map<ChangeTypeEnum, List<UploadLegalBankDTO>> legalBankMap, UserInfo currentUser, Map<Long, Integer> serialToCounterpartyIdMap, Map<Long, Integer> counterpartyVersionMap) {
        // 处理新增的银行账户
        List<UploadLegalBankDTO> createList = legalBankMap.get(ChangeTypeEnum.CREATE);
        if (createList != null && !createList.isEmpty()) {
            log.info("开始处理新增银行账户数据，共{}条", createList.size());
            for (UploadLegalBankDTO dto : createList) {
                createBankFromUpload(dto, currentUser, serialToCounterpartyIdMap, counterpartyVersionMap);
            }
        }

        // 处理修改的银行账户
        List<UploadLegalBankDTO> updateList = legalBankMap.get(ChangeTypeEnum.UPDATE);
        if (updateList != null && !updateList.isEmpty()) {
            log.info("开始处理修改银行账户数据，共{}条", updateList.size());
            for (UploadLegalBankDTO dto : updateList) {
                updateBankFromUpload(dto, currentUser, counterpartyVersionMap);
            }
        }
    }

    private void updateBankFromUpload(UploadLegalBankDTO dto, UserInfo currentUser, Map<Long, Integer> counterpartyVersionMap) {
        try {
            if (dto.getId() == null) {
                log.warn("银行账户ID为空，无法更新：{}", dto.getAccountNumber());
                return;
            }

            // 查找现有银行信息
            BankInfoEntity existingBank = bankInfoMapper.selectById(dto.getId());
            if (existingBank == null) {
                log.warn("未找到要更新的银行账户：ID={}", dto.getId());
                return;
            }

            // 根据法人实体编码或序号查找法人实体（修改银行账户时，序号映射表为空，直接使用现有逻辑）
            LegalEntityDetailEntity counterparty = findLegalByCodeOrSerial(dto.getLegalEntityCode(), dto.getSerialNumber(), new HashMap<>());
            if (counterparty == null) {
                log.warn("未找到对应法人实体，无法更新银行账户：编码={}, 序号={}", dto.getLegalEntityCode(), dto.getSerialNumber());
                return;
            }

            // 记录原账号（用于变更记录）
            String oldAccountNumber = existingBank.getAccountNumber();

            // 删除原有的银行账户记录
            int deleteResult = bankInfoMapper.deleteById(dto.getId());
            if (deleteResult > 0) {
                log.info("成功删除原银行账户：{}", oldAccountNumber);

                // 记录删除操作的变更记录（使用预分配版本号）
                Integer preAllocatedVersion = counterpartyVersionMap.get(counterparty.getId().longValue());
                if (preAllocatedVersion != null) {
                    saveBankChangeRecordWithVersion(counterparty.getId().longValue(), "bank", oldAccountNumber, null,
                            Long.parseLong(currentUser.getUserId()), currentUser.getNickName(), preAllocatedVersion);
                } else {
                    saveBankChangeRecord(counterparty.getId().longValue(), "bank", oldAccountNumber, null,
                            Long.parseLong(currentUser.getUserId()), currentUser.getNickName());
                }
            }

            // 创建新的银行账户
            CreateBankInfoReqDTO bankReqDTO = new CreateBankInfoReqDTO();
            bankReqDTO.setBankCode("银行账户" + System.currentTimeMillis());
            bankReqDTO.setBankCountry(dto.getBankCountry());
            bankReqDTO.setBankName(dto.getBankName());
            bankReqDTO.setAccountName(dto.getAccountName());
            bankReqDTO.setAccountNumber(dto.getAccountNumber());
            bankReqDTO.setBankInternalId(dto.getBankInternalId());
            bankReqDTO.setBindingType("法人");
            bankReqDTO.setBindingId(counterparty.getId().toString());

            // 创建新银行信息
            CreateBankInfoRespDTO respDTO = bankInfoService.createBankInfo(bankReqDTO);
            if (respDTO != null && respDTO.getId() != null) {
                log.info("成功为法人实体{}创建新银行账户：{}", counterparty.getLegalEntityName(), dto.getAccountNumber());

                // 记录新增操作的变更记录（使用预分配版本号）
                Integer preAllocatedVersion = counterpartyVersionMap.get(counterparty.getId().longValue());
                if (preAllocatedVersion != null) {
                    saveBankChangeRecordWithVersion(counterparty.getId().longValue(), "bank", null, dto.getAccountNumber(),
                            Long.parseLong(currentUser.getUserId()), currentUser.getNickName(), preAllocatedVersion);
                } else {
                    saveBankChangeRecord(counterparty.getId().longValue(), "bank", null, dto.getAccountNumber(),
                            Long.parseLong(currentUser.getUserId()), currentUser.getNickName());
                }
            } else {
                log.error("为法人实体{}创建新银行账户失败：{}", counterparty.getLegalEntityName(), dto.getAccountNumber());
                throw new RuntimeException("创建新银行账户失败：" + dto.getAccountNumber());
            }

        } catch (Exception e) {
            log.error("更新银行账户失败：{}, 错误信息：{}", dto.getAccountNumber(), e.getMessage(), e);
            throw new RuntimeException("更新银行账户失败：" + dto.getAccountNumber(), e);
        }
    }

    private void saveBankChangeRecordWithVersion(Long legalEntityId, String field, String oldValue, String newValue, Long userId, String username, Integer version) {
        try {
            // 创建变更记录
            ChangeRecord changeRecord = new ChangeRecord();
            changeRecord.setLegalId(legalEntityId);
            changeRecord.setVersion(version);
            changeRecord.setField(field);
            changeRecord.setOldValue(oldValue);
            changeRecord.setNewValue(newValue);
            changeRecord.setCreateId(userId);
            changeRecord.setCreateBy(username);
            changeRecord.setCreateTime(new Date());

            // 保存变更记录
            changeRecordService.save(changeRecord);

            log.info("保存银行账户变更记录成功，法人ID: {}, 预分配版本号: {}, 字段: {}, 旧值: {}, 新值: {}",
                    legalEntityId, version, field, oldValue, newValue);
        } catch (Exception e) {
            log.error("保存银行账户变更记录失败：{}", e.getMessage(), e);
        }
    }

    private void saveBankChangeRecord(Long legalEntityId, String field, String oldValue, String newValue, Long userId, String username) {
        try {
            // 获取当前最大版本号
            Integer currentMaxVersion = getCurrentMaxVersion(legalEntityId);
            Integer newVersion = (currentMaxVersion != null ? currentMaxVersion : 0) + 1;

            // 创建变更记录
            ChangeRecord changeRecord = new ChangeRecord();
            changeRecord.setLegalId(legalEntityId);
            changeRecord.setVersion(newVersion);
            changeRecord.setField(field);
            changeRecord.setOldValue(oldValue);
            changeRecord.setNewValue(newValue);
            changeRecord.setCreateId(userId);
            changeRecord.setCreateBy(username);
            changeRecord.setCreateTime(new Date());

            // 保存变更记录
            changeRecordService.save(changeRecord);

            log.info("保存银行账户变更记录成功，法人ID: {}, 版本号: {}, 字段: {}, 旧值: {}, 新值: {}",
                    legalEntityId, newVersion, field, oldValue, newValue);
        } catch (Exception e) {
            log.error("保存银行账户变更记录失败：{}", e.getMessage(), e);
        }
    }

    private void createBankFromUpload(UploadLegalBankDTO dto, UserInfo currentUser, Map<Long, Integer> serialToCounterpartyIdMap, Map<Long, Integer> counterpartyVersionMap) {
        try {
            // 根据法人实体编码或序号查找法人实体
            LegalEntityDetailEntity counterparty = findLegalByCodeOrSerial(dto.getLegalEntityCode(), dto.getSerialNumber(), serialToCounterpartyIdMap);
            if (counterparty == null) {
                log.warn("未找到对应法人实体，无法创建银行账户：编码={}, 序号={}", dto.getLegalEntityCode(), dto.getSerialNumber());
                return;
            }

            // 创建银行信息请求DTO
            CreateBankInfoReqDTO bankReqDTO = new CreateBankInfoReqDTO();
            bankReqDTO.setBankCode("银行账户" + System.currentTimeMillis());
            bankReqDTO.setBankCountry(dto.getBankCountry());
            bankReqDTO.setBankName(dto.getBankName());
            bankReqDTO.setAccountName(dto.getAccountName());
            bankReqDTO.setAccountNumber(dto.getAccountNumber());
            bankReqDTO.setBankInternalId(dto.getBankInternalId());
            bankReqDTO.setBindingType("法人");
            bankReqDTO.setBindingId(counterparty.getId().toString());

            // 创建银行信息
            CreateBankInfoRespDTO respDTO = bankInfoService.createBankInfo(bankReqDTO);
            if (respDTO != null && respDTO.getId() != null) {
                log.info("成功为法人实体{}创建银行账户：{}", counterparty.getLegalEntityName(), dto.getAccountNumber());

                // 记录银行账户新增的变更记录（使用预分配版本号）
                Integer preAllocatedVersion = counterpartyVersionMap.get(counterparty.getId().longValue());
                if (preAllocatedVersion != null) {
                    saveBankChangeRecordWithVersion(counterparty.getId().longValue(), "bank", null, dto.getAccountNumber(),
                            Long.parseLong(currentUser.getUserId()), currentUser.getNickName(), preAllocatedVersion);
                }
            } else {
                log.error("为法人实体{}创建银行账户失败：{}", counterparty.getLegalEntityName(), dto.getAccountNumber());
            }
        } catch (Exception e) {
            log.error("创建银行账户失败：{}, 错误信息：{}", dto.getAccountNumber(), e.getMessage(), e);
            throw new RuntimeException("创建银行账户失败：" + dto.getAccountNumber(), e);
        }
    }

    private void processLegalData(Map<ChangeTypeEnum, List<UploadLegalDTO>> legalMap, UserInfo currentUser, Map<Long, Integer> serialToCounterpartyIdMap, Map<Long, Integer> counterpartyVersionMap) {
        // 处理新增的法人实体
        List<UploadLegalDTO> createList = legalMap.get(ChangeTypeEnum.CREATE);
        if (createList != null && !createList.isEmpty()) {
            log.info("开始处理新增法人数据，共{}条", createList.size());
            for (UploadLegalDTO dto : createList) {
                Integer legalId = createLegalFromUpload(dto, currentUser);
                // 记录序号到法人实体ID的映射
                if (dto.getSerialNumber() != null && legalId != null) {
                    serialToCounterpartyIdMap.put(dto.getSerialNumber(), legalId);
                    log.info("记录序号映射：序号{}对应法人实体ID{}", dto.getSerialNumber(), legalId);
                }
            }
        }

        // 处理修改的法人实体
        List<UploadLegalDTO> updateList = legalMap.get(ChangeTypeEnum.UPDATE);
        if (updateList != null && !updateList.isEmpty()) {
            log.info("开始处理修改法人实体数据，共{}条", updateList.size());
            for (UploadLegalDTO dto : updateList) {
                updateLegalFromUpload(dto, currentUser, counterpartyVersionMap);
            }
        }
    }

    /**
     * 根据上传数据修改法人实体
     * @param dto
     * @param currentUser
     * @param counterpartyVersionMap
     */
    private void updateLegalFromUpload(UploadLegalDTO dto, UserInfo currentUser, Map<Long, Integer> counterpartyVersionMap) {
        try {
            // 根据法人实体编码或序号查找现有法人实体
            LegalEntityDetailEntity existingEntity = findExistingLegal(dto);
            if (existingEntity == null) {
                log.warn("未找到要更新的法人实体：编码={}, 序号={}", dto.getLegalEntityCode(), dto.getSerialNumber());
                return;
            }

            // 记录变更前的数据，用于变更记录
            LegalEntityDetailEntity oldEntity = new LegalEntityDetailEntity();
            BeanUtils.copyProperties(existingEntity, oldEntity);

            // 更新字段
            if (StringUtils.hasText(dto.getLegalEntityName())) {
                existingEntity.setLegalEntityName(dto.getLegalEntityName());
            }
            if (StringUtils.hasText(dto.getRegisterCountry())) {
                existingEntity.setRegisterCountry(dto.getRegisterCountry());
            }
            if (StringUtils.hasText(dto.getLegalEntityName())) {
                existingEntity.setLegalEntityName(dto.getLegalEntityName());
            }
            if (StringUtils.hasText(dto.getLegalEntityAbbr())) {
                existingEntity.setLegalEntityAbbr(dto.getLegalEntityAbbr());
            }
            if (StringUtils.hasText(dto.getDetailAddress())) {
                existingEntity.setDetailAddress(dto.getDetailAddress());
            }
            if (dto.getCertificateType() != null) {
                existingEntity.setCertificateType(dto.getCertificateType());
            }
            if (StringUtils.hasText(dto.getCertificateId())) {
                existingEntity.setCertificateId(dto.getCertificateId());
            }

            // 更新审计字段
            existingEntity.setUpdateAppid("upload");
            existingEntity.setUpdateSource("upload");
            existingEntity.setUpdateBy(currentUser.getNickName());
            existingEntity.setUpdatedAt(LocalDateTime.now());

            // 保存变更记录（使用预分配的版本号）
            Integer preAllocatedVersion = counterpartyVersionMap.get(existingEntity.getId().longValue());
            if (preAllocatedVersion != null) {
                saveChangeRecordForUploadWithVersion(oldEntity, existingEntity, Long.parseLong(currentUser.getUserId()), currentUser.getNickName(), preAllocatedVersion);
            } else {
                saveChangeRecordForUpload(oldEntity, existingEntity, Long.parseLong(currentUser.getUserId()), currentUser.getNickName());
            }

            // 更新到数据库
            int result = legalEntityDetailMapper.updateById(existingEntity);
            if (result > 0) {
                log.info("成功更新法人实体：{} - {}", existingEntity.getLegalEntityCode(), existingEntity.getLegalEntityName());
            } else {
                log.error("更新法人实体失败：{} - {}", dto.getLegalEntityCode(), dto.getLegalEntityName());
            }
        } catch (Exception e) {
            log.error("更新法人实体失败：{} - {}, 错误信息：{}", dto.getLegalEntityCode(), dto.getLegalEntityName(), e.getMessage(), e);
            throw new RuntimeException("更新法人实体失败：" + dto.getLegalEntityName(), e);
        }
    }

    private void saveChangeRecordForUploadWithVersion(LegalEntityDetailEntity oldEntity, LegalEntityDetailEntity newEntity, Long userId, String username, Integer version) {
        try {
            List<ChangeRecord> changeRecords = new ArrayList<>();

            // 比较各个字段并记录变更
            if (!Objects.equals(oldEntity.getLegalEntityName(), newEntity.getLegalEntityName())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), version, "legalEntityName",
                        oldEntity.getLegalEntityName(), newEntity.getLegalEntityName(), userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getRegisterCountry(), newEntity.getRegisterCountry())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), version, "registerCountry",
                        oldEntity.getRegisterCountry(), newEntity.getRegisterCountry(), userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getLegalEntityAbbr(), newEntity.getLegalEntityAbbr())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), version, "legalEntityAbbr",
                        oldEntity.getLegalEntityAbbr(), newEntity.getLegalEntityAbbr(), userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getDetailAddress(), newEntity.getDetailAddress())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), version, "detailAddress",
                        oldEntity.getDetailAddress(), newEntity.getDetailAddress(), userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getCertificateType(), newEntity.getCertificateType())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), version, "certificateType",
                        oldEntity.getCertificateType() != null ? oldEntity.getCertificateType().toString() : null,
                        newEntity.getCertificateType() != null ? newEntity.getCertificateType().toString() : null,
                        userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getCertificateId(), newEntity.getCertificateId())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), version, "certificateId",
                        oldEntity.getCertificateId(), newEntity.getCertificateId(), userId, username);
                changeRecords.add(record);
            }

            // 批量保存变更记录
            if (!changeRecords.isEmpty()) {
                changeRecordService.saveBatch(changeRecords);
                log.info("保存上传操作变更记录成功，法人ID: {}, 预分配版本号: {}, 变更记录数: {}",
                        newEntity.getId(), version, changeRecords.size());
            }
        } catch (Exception e) {
            log.error("保存上传操作变更记录失败：{}", e.getMessage(), e);
        }
    }

    /**
     * 为上传操作保存变更记录
     */
    private void saveChangeRecordForUpload(LegalEntityDetailEntity oldEntity, LegalEntityDetailEntity newEntity, Long userId, String username) {
        try {
            // 获取当前最大版本号
            Integer currentMaxVersion = getCurrentMaxVersion(newEntity.getId().longValue());
            Integer newVersion = (currentMaxVersion != null ? currentMaxVersion : 0) + 1;

            List<ChangeRecord> changeRecords = new ArrayList<>();

            // 比较各个字段并记录变更
            if (!Objects.equals(oldEntity.getLegalEntityName(), newEntity.getLegalEntityName())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), newVersion, "legalEntityName",
                        oldEntity.getLegalEntityName(), newEntity.getLegalEntityName(), userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getRegisterCountry(), newEntity.getRegisterCountry())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), newVersion, "registerCountry",
                        oldEntity.getRegisterCountry(), newEntity.getRegisterCountry(), userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getLegalEntityAbbr(), newEntity.getLegalEntityAbbr())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), newVersion, "legalEntityAbbr",
                        oldEntity.getLegalEntityAbbr(), newEntity.getLegalEntityAbbr(), userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getDetailAddress(), newEntity.getDetailAddress())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), newVersion, "detailAddress",
                        oldEntity.getDetailAddress(), newEntity.getDetailAddress(), userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getCertificateType(), newEntity.getCertificateType())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), newVersion, "certificateType",
                        oldEntity.getCertificateType() != null ? oldEntity.getCertificateType().toString() : null,
                        newEntity.getCertificateType() != null ? newEntity.getCertificateType().toString() : null,
                        userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getCertificateId(), newEntity.getCertificateId())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), newVersion, "certificateId",
                        oldEntity.getCertificateId(), newEntity.getCertificateId(), userId, username);
                changeRecords.add(record);
            }

            // 批量保存变更记录
            if (!changeRecords.isEmpty()) {
                changeRecordService.saveBatch(changeRecords);
                log.info("保存上传操作变更记录成功，法人ID: {}, 版本号: {}, 变更记录数: {}",
                        newEntity.getId(), newVersion, changeRecords.size());
            }
        } catch (Exception e) {
            log.error("保存上传操作变更记录失败：{}", e.getMessage(), e);
        }
    }

    /**
     * 创建变更记录
     */
    private ChangeRecord createChangeRecord(Long legalEntityId, Integer version, String field,
                                            String oldValue, String newValue, Long userId, String username) {
        ChangeRecord changeRecord = new ChangeRecord();
        changeRecord.setLegalId(legalEntityId);
        changeRecord.setVersion(version);
        changeRecord.setField(field);
        changeRecord.setOldValue(oldValue);
        changeRecord.setNewValue(newValue);
        changeRecord.setCreateId(userId);
        changeRecord.setCreateBy(username);
        changeRecord.setCreateTime(new Date());
        return changeRecord;
    }

    /**
     * 从上传数据创建法人实体
     */
    private Integer createLegalFromUpload(UploadLegalDTO dto, UserInfo currentUser) {
        try {
            LegalEntityDetailEntity entity = new LegalEntityDetailEntity();

            // 设置基本信息
            entity.setLegalEntityCode(StringUtils.hasText(dto.getLegalEntityCode()) ?
                    dto.getLegalEntityCode() : generateLegalEntityCode());
            entity.setLegalEntityName(dto.getLegalEntityName());
            entity.setRegisterCountry(dto.getRegisterCountry());
            entity.setLegalEntityAbbr(dto.getLegalEntityAbbr());
            entity.setDetailAddress(dto.getDetailAddress());
            entity.setCertificateType(dto.getCertificateType());
            entity.setCertificateId(dto.getCertificateId());

            // 设置审计字段
            entity.setIsEnabled(true);
            entity.setCreateAppid("upload");
            entity.setCreateSource("upload");
            entity.setUpdateAppid("upload");
            entity.setUpdateSource("upload");
            entity.setCreateId(Long.parseLong(currentUser.getUserId()));
            entity.setCreateBy(currentUser.getNickName());
            entity.setCreatedAt(LocalDateTime.now());
            entity.setUpdateBy(currentUser.getNickName());
            entity.setUpdatedAt(LocalDateTime.now());

            // 保存到数据库
            int result = legalEntityDetailMapper.insert(entity);
            if (result > 0) {
                log.info("成功创建法人实体：{} - {}", entity.getLegalEntityCode(), entity.getLegalEntityName());
                return entity.getId();
            } else {
                log.error("创建法人实体失败：{} - {}", dto.getLegalEntityCode(), dto.getLegalEntityName());
                throw new RuntimeException("创建法人实体失败：" + dto.getLegalEntityName());
            }
        } catch (Exception e) {
            log.error("创建法人实体失败：{} - {}, 错误信息：{}", dto.getLegalEntityCode(), dto.getLegalEntityName(), e.getMessage(), e);
            throw new RuntimeException("创建法人实体失败：" + dto.getLegalEntityName(), e);
        }

    }

    /**
     * 预分配版本号，确保同一个法人的所有操作使用相同版本
     */
    private Map<Long, Integer> preAllocateVersions(List<UploadLegalDTO> legalDataList, List<UploadLegalBankDTO> legalBankDataList) {
        HashMap<Long, Integer> legalVersionMap = new HashMap<>();
        Set<Long> affectedLegalIds = new HashSet<>();

        // 收集所有需要修改的法人ID
        for (UploadLegalDTO dto : legalDataList) {
            if (dto.getType() == ChangeTypeEnum.UPDATE) {
                LegalEntityDetailEntity existingLegal = findExistingLegal(dto);
                if (existingLegal != null) {
                    affectedLegalIds.add(existingLegal.getId().longValue());
                }
            }
        }

        // 收集银行账户相关的法人ID
        for (UploadLegalBankDTO dto : legalBankDataList) {
            LegalEntityDetailEntity existingLegal = findLegalByCodeOrSerial(dto.getLegalEntityCode(), dto.getSerialNumber(), new HashMap<>());
            if (null != existingLegal) {
                affectedLegalIds.add(existingLegal.getId().longValue());
            }
        }

        // 为每个受影响的法人预分配版本号
        for (Long affectedLegalId : affectedLegalIds) {
            Integer currentMaxVersion = getCurrentMaxVersion(affectedLegalId);
            Integer newVersion = (currentMaxVersion != null ? currentMaxVersion : 0) + 1;
            legalVersionMap.put(affectedLegalId, newVersion);
            log.info("为法人实体ID: {}预分配版本号: {}", affectedLegalId, newVersion);
        }

        return legalVersionMap;
    }

    private LegalEntityDetailEntity findLegalByCodeOrSerial(String legalEntityCode, Long serialNumber, Map<Long, Integer> serialToCounterpartyIdMap) {
        if (StringUtils.hasText(legalEntityCode)) {
            LambdaQueryWrapper<LegalEntityDetailEntity> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(LegalEntityDetailEntity::getLegalEntityCode, legalEntityCode);
            return legalEntityDetailMapper.selectOne(wrapper);
        } else if (serialNumber != null) {
            // 先从映射表中查找新创建的法人实体
            Integer legalEntityId = serialToCounterpartyIdMap.get(serialNumber);
            if (legalEntityId != null) {
                return legalEntityDetailMapper.selectById(legalEntityId);
            }
            // 如果映射表中没有，再从数据库中按ID查找（兼容旧数据）
            return legalEntityDetailMapper.selectById(serialNumber.intValue());
        }
        return null;
    }

    private LegalEntityDetailEntity findExistingLegal(UploadLegalDTO dto) {
        if (StringUtils.hasText(dto.getLegalEntityCode())) {
            // 按法人实体编码查找
            LambdaQueryWrapper<LegalEntityDetailEntity> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(LegalEntityDetailEntity::getLegalEntityCode, dto.getLegalEntityCode());
            return legalEntityDetailMapper.selectOne(wrapper);
        } else if (dto.getSerialNumber() != null) {
            // 按序号查找（这里假设序号就是ID，根据实际情况调整）
            return legalEntityDetailMapper.selectById(dto.getSerialNumber().intValue());
        }
        return null;
    }

    private List<UploadLegalBankDTO> parseLegalBankExcelFile(MultipartFile file) {
        ArrayList<UploadLegalBankDTO> dataList = new ArrayList<>();
        try (XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream())) {
            XSSFSheet sheet = workbook.getSheetAt(2);
            for (int i = 2; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;
                UploadLegalBankDTO data = parseRowToLegalBankData(row, i + 1);
                if (data != null) {
                    dataList.add(data);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return dataList;
    }

    private UploadLegalBankDTO parseRowToLegalBankData(Row row, int rowNum) {
        try {
            UploadLegalBankDTO result = new UploadLegalBankDTO();
            // 变更类型
            result.setType(ChangeTypeEnum.getByDesc(row.getCell(0).getStringCellValue()));
            //银行信息ID
            result.setId(null == row.getCell(1) ? null : (int) row.getCell(1).getNumericCellValue());
            // 基础信息序号
            result.setSerialNumber(null == row.getCell(2) ? null : (long) row.getCell(2).getNumericCellValue());
            // 法人实体编号
            result.setLegalEntityCode(null == row.getCell(3) ? null : row.getCell(3).getStringCellValue());
            // 账号
            result.setAccountNumber(row.getCell(4).getStringCellValue());
            // 账号名
            result.setAccountName(row.getCell(5).getStringCellValue());
            // 银行内部唯一ID
            result.setBankInternalId(null == row.getCell(6) ? null : row.getCell(6).getStringCellValue());
            // 银行名称
            result.setBankName(row.getCell(7).getStringCellValue());
            // 银行国家
            result.setBankCountry(CountryEnum.getCountryCode(row.getCell(8).getStringCellValue()));
            // 币种
            result.setCurrency(null == row.getCell(9) ? null : row.getCell(9).getStringCellValue());
            // 总账科目
            result.setGeneralLedgerAccount(null == row.getCell(10) ? null : row.getCell(10).getStringCellValue());
            // 结算账户
            result.setClearingAccount(null == row.getCell(11) ? null : row.getCell(11).getStringCellValue());
            // 账户属性描述
            result.setAccountAttributeDesc(null == row.getCell(12) ? null : row.getCell(12).getStringCellValue());
            return result;
        } catch (Exception e) {
            log.warn("解析第{}行数据失败：{}", rowNum, e.getMessage());
            return null;
        }
    }

    private List<UploadLegalDTO> parseLegalExcelFile(MultipartFile file) {
        ArrayList<UploadLegalDTO> dataList = new ArrayList<>();

        try (XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream())) {
            XSSFSheet sheet = workbook.getSheetAt(0);

            // 跳过表头和模版提示，从第三行开始读取
            for (int i = 2; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;
                UploadLegalDTO data = parseRowToLegal(row, i + 1);
                if (data != null) {
                    dataList.add(data);
                }
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return dataList;
    }

    private UploadLegalDTO parseRowToLegal(Row row, int rowNum) {
        try {
            UploadLegalDTO result = new UploadLegalDTO();
            // 变更类型
            result.setType(ChangeTypeEnum.getByDesc(row.getCell(0).getStringCellValue()));
            // 基础信息序号
            result.setSerialNumber(null == row.getCell(1) ? null : (long) row.getCell(1).getNumericCellValue());
            // 法人实体编号
            result.setLegalEntityCode(null == row.getCell(2) ? null : row.getCell(2).getStringCellValue());
            // 法人名称
            result.setLegalEntityName(row.getCell(3).getStringCellValue());
            // 法人简称
            result.setLegalEntityAbbr(null == row.getCell(4) ? null : row.getCell(4).getStringCellValue());
            // 注册国家
            result.setRegisterCountry(row.getCell(5).getStringCellValue());
            // 详细地址
            result.setDetailAddress(null == row.getCell(6) ? null : row.getCell(6).getStringCellValue());
            // 证件类型
            result.setCertificateType(null == row.getCell(7) ? null : CertificateTypeEnum.getCodeByName(row.getCell(7).getStringCellValue()));
            // 证件ID
            result.setCertificateId(null == row.getCell(8) ? null : row.getCell(8).getStringCellValue());
            return result;
        } catch (Exception e) {
            log.warn("解析第{}行数据失败：{}", rowNum, e.getMessage());
            return null;
        }
    }

    /**
     * 生成法人实体编码
     *
     * @return 法人实体编码
     */
    private String generateLegalEntityCode() {
        // 简单实现：使用时间戳 + 随机数
        return "LE" + System.currentTimeMillis() + String.format("%03d", (int) (Math.random() * 1000));
    }

    /**
     * 判断客户端类型
     *
     * @param request HTTP请求对象
     * @return 客户端类型：mobile（手机端）、web（网页端）、UNKNOWN（未知）
     */
    private String determineClientType(HttpServletRequest request) {
        String userAgent = request.getHeader("User-Agent");
        if (userAgent != null) {
            userAgent = userAgent.toLowerCase();
            // 检查是否包含移动设备标识
            if (userAgent.contains("mobile") ||
                    userAgent.contains("android") ||
                    userAgent.contains("iphone") ||
                    userAgent.contains("ipad") ||
                    userAgent.contains("blackberry") ||
                    userAgent.contains("windows phone")) {
                return "mobile";
            }
            // 检查是否为桌面浏览器
            if (userAgent.contains("mozilla") ||
                    userAgent.contains("chrome") ||
                    userAgent.contains("safari") ||
                    userAgent.contains("firefox") ||
                    userAgent.contains("edge")) {
                return "web";
            }
        }

        return "UNKNOWN";
    }

    /**
     * 创建银行信息并绑定到法人实体
     *
     * @param bankInfoList  银行信息列表
     * @param legalEntityId 法人实体ID
     */
    private void createAndBindBankInfosForLegal(List<BankInfoDTO> bankInfoList, Integer legalEntityId) {
        if (bankInfoList == null || bankInfoList.isEmpty()) {
            return;
        }

        for (int i = 0; i < bankInfoList.size(); i++) {
            BankInfoDTO bankInfo = bankInfoList.get(i);
            // 转换为创建银行信息的请求DTO
            CreateBankInfoReqDTO bankReqDTO = new CreateBankInfoReqDTO();
            int number = i + 1;
            bankReqDTO.setBankCode("银行账户" + number);
            bankReqDTO.setBankCountry(bankInfo.getBankCountry());
            bankReqDTO.setBankName(bankInfo.getBankName());
            bankReqDTO.setAccountName(bankInfo.getAccountName());
            bankReqDTO.setAccountNumber(bankInfo.getAccountNumber());
            bankReqDTO.setBankInternalId(bankInfo.getBankInternalId());
            bankReqDTO.setCurrency(bankInfo.getCurrency());
            bankReqDTO.setGeneralLedgerAccount(bankInfo.getGeneralLedgerAccount());
            bankReqDTO.setClearingAccount(bankInfo.getClearingAccount());
            bankReqDTO.setAccountAttributeDesc(bankInfo.getAccountAttributeDesc());

            // 设置绑定信息
            bankReqDTO.setBindingType("法人");
            bankReqDTO.setBindingId(legalEntityId.toString());

            // 创建银行信息
            bankInfoService.createBankInfo(bankReqDTO);
        }
    }

    /**
     * 将实体转换为列表项DTO
     */
    private LegalEntityPageRespDTO.LegalEntityItemDTO convertToItemDTO(LegalEntityDetailEntity entity) {
        LegalEntityPageRespDTO.LegalEntityItemDTO itemDTO = new LegalEntityPageRespDTO.LegalEntityItemDTO();
        BeanUtils.copyProperties(entity, itemDTO);

        // 设置状态文本
        itemDTO.setStatusText(Boolean.TRUE.equals(entity.getIsEnabled()) ? "启用" : "停用");

        // 获取证件类型描述
        itemDTO.setCertificateTypeDesc(convertCertificateTypeToDesc(entity.getCertificateType()));

        // 转换国家名称
        itemDTO.setRegisterCountryName(convertCountryCodeToName(entity.getRegisterCountry()));

        // 处理银行账户信息
        if (entity.getBankInfoIds() != null && !entity.getBankInfoIds().isEmpty()) {
            String[] bankIdStrings = entity.getBankInfoIds().split(",");
            List<Integer> bankIds = new ArrayList<>();
            for (String bankIdStr : bankIdStrings) {
                try {
                    bankIds.add(Integer.parseInt(bankIdStr.trim()));
                } catch (NumberFormatException e) {
                    log.warn("银行ID格式错误: {}", bankIdStr);
                }
            }

            itemDTO.setBankAccountCount(bankIds.size());

            // 查询银行信息
            if (!bankIds.isEmpty()) {
                List<BankInfoEntity> bankInfos = bankInfoMapper.selectByIds(bankIds);
                List<BankInfoDTO> bankInfoDTOs = bankInfos.stream()
                        .map(bank -> {
                            BankInfoDTO bankDTO = new BankInfoDTO();
                            BeanUtils.copyProperties(bank, bankDTO);
                            // 设置银行国家代码和名称
                            bankDTO.setBankCountryCode(bank.getBankCountry()); // 国家代码
                            bankDTO.setBankCountry(bank.getBankCountry()); // 国家代码
                            bankDTO.setBankCountryName(countryDictService.getCountryNameByCode(bank.getBankCountry())); // 国家名称
                            // 设置兼容字段
                            bankDTO.setBankAccount(bank.getAccountNumber()); // 兼容字段，值同accountNumber
                            bankDTO.setCurrency(bank.getCurrency());
                            return bankDTO;
                        })
                        .collect(Collectors.toList());
                itemDTO.setBankInfoList(bankInfoDTOs);
            } else {
                itemDTO.setBankInfoList(new ArrayList<>());
            }
        } else {
            itemDTO.setBankAccountCount(0);
            itemDTO.setBankInfoList(new ArrayList<>());
        }

        if (StringUtils.hasText(entity.getAttachment())) {
            List<Long> fieldIds = Arrays.stream(entity.getAttachment().split(",")).map(Long::parseLong).toList();
            List<LegalEntityPageRespDTO.FileInfoDTO> files = fileInfoService.listByIds(fieldIds)
                    .stream().map(fileInfo -> {
                        LegalEntityPageRespDTO.FileInfoDTO fileInfoDTO = new LegalEntityPageRespDTO.FileInfoDTO();
                        fileInfoDTO.setFileId(fileInfo.getFileId());
                        fileInfoDTO.setFileName(fileInfo.getFileName());
                        fileInfoDTO.setFileType(fileInfo.getFileType());
                        fileInfoDTO.setFileKey(fileInfo.getFileKey());
                        fileInfoDTO.setFileSize(fileInfo.getFileSize());
                        return fileInfoDTO;
                    }).toList();
            itemDTO.setAttachments(files);
        }

        // 查询租户信息
        List<String> sysTenantKeys = legalTenantRelMapper.selectList(Wrappers.<LegalTenantRel>lambdaQuery()
                        .eq(LegalTenantRel::getLegalId, entity.getId())
                        .select(LegalTenantRel::getSysTenantKey))
                .stream()
                .map(LegalTenantRel::getSysTenantKey)
                .collect(Collectors.toList());
        itemDTO.setSysTenantKey(sysTenantKeys);

        // 设置租户名称（当前只支持一个租户）
        if (!CollectionUtils.isEmpty(sysTenantKeys)) {
            String tenantName = sysDepartmentMapper.selectList(Wrappers.<SysDepartment>lambdaQuery()
                            .in(SysDepartment::getSysTenantKey,sysTenantKeys)
                            .and(wrapper -> wrapper.eq(SysDepartment::getPid, 0)
                                    .or()
                                    .eq(SysDepartment::getIndependentFlag, 1)))
                    .stream().map(SysDepartment::getName).collect(Collectors.joining(","));
            itemDTO.setTenantName(tenantName);
        }

        return itemDTO;
    }

    /**
     * 将国家代码转换为国家名称
     *
     * @param countryCode 国家代码
     * @return 国家名称
     */
    private String convertCountryCodeToName(String countryCode) {
        if (countryCode == null) {
            return null;
        }
        return countryDictService.getCountryNameByCode(countryCode);
    }


    /**
     * 将证件类型code转换为描述
     *
     * @param certTypeCode 证件类型code
     * @return 证件类型描述
     */
    private String convertCertificateTypeToDesc(Integer certTypeCode) {
        if (certTypeCode == null) {
            return null;
        }
        CertificateTypeEnum certEnum = CertificateTypeEnum.getByCode(certTypeCode);
        return certEnum != null ? certEnum.getName() : null;
    }

    /**
     * 比较法人实体字段变化并保存修改记录（基于DTO）
     *
     * @param originalEntity 原始实体
     * @param dto            更新DTO
     * @param legalId        法人实体ID
     * @param createId       创建人ID
     * @param createBy       创建人
     */
    private void compareAndSaveChangeRecordByDto(LegalEntityDetailEntity originalEntity, UpdateLegalInfoDTO dto, Long legalId, Long createId, String createBy) {
        // 获取当前最大版本号
        Integer currentMaxVersion = getCurrentMaxVersion(legalId);
        Integer newVersion = (currentMaxVersion != null ? currentMaxVersion : 0) + 1;

        // 比较字段并保存修改记录
        Map<String, Map<String, String>> fieldChanges = compareFieldsByDto(originalEntity, dto);
        Map<String, List<String>> bankChanges = compareBanks(originalEntity, dto);
        Map<String, List<String>> attachmentChanges = compareAttachments(originalEntity, dto);

        // 判断是否有字段变更、银行账户变更或附件变更
        boolean hasFieldChanges = !fieldChanges.isEmpty();
        List<String> bankAddList = bankChanges.get("addList");
        List<String> bankDeleteList = bankChanges.get("deleteList");
        boolean hasBankChanges = (bankAddList != null && !bankAddList.isEmpty()) || (bankDeleteList != null && !bankDeleteList.isEmpty());

        List<String> attachmentAddList = attachmentChanges.get("addList");
        List<String> attachmentDeleteList = attachmentChanges.get("deleteList");
        boolean hasAttachmentChanges = (attachmentAddList != null && !attachmentAddList.isEmpty()) || (attachmentDeleteList != null && !attachmentDeleteList.isEmpty());

        if (hasFieldChanges || hasBankChanges || hasAttachmentChanges) {
            List<ChangeRecord> changeRecords = new ArrayList<>();

            if (hasFieldChanges) {
                // 处理字段变更记录
                for (Map.Entry<String, Map<String, String>> entry : fieldChanges.entrySet()) {
                    String fieldName = entry.getKey();
                    Map<String, String> values = entry.getValue();

                    ChangeRecord changeRecord = new ChangeRecord();
                    changeRecord.setLegalId(legalId);
                    changeRecord.setVersion(newVersion);
                    changeRecord.setField(fieldName);
                    changeRecord.setNewValue(values.get("newValue"));
                    changeRecord.setOldValue(values.get("oldValue"));
                    changeRecord.setCreateId(createId);
                    changeRecord.setCreateBy(createBy);
                    changeRecord.setCreateTime(new Date());

                    changeRecords.add(changeRecord);
                }
            }


            // 处理银行账户变更记录
            // 新增的银行账户：旧值为null，新值为账号
            if (bankAddList != null) {
                for (String accountNumber : bankAddList) {
                    ChangeRecord changeRecord = new ChangeRecord();
                    changeRecord.setLegalId(legalId);
                    changeRecord.setVersion(newVersion);
                    changeRecord.setField("bank");
                    changeRecord.setOldValue(null);
                    changeRecord.setNewValue(accountNumber);
                    changeRecord.setCreateId(createId);
                    changeRecord.setCreateBy(createBy);
                    changeRecord.setCreateTime(new Date());

                    changeRecords.add(changeRecord);
                }
            }

            // 删除的银行账户：旧值为账号，新值为null
            if (bankDeleteList != null) {
                for (String accountNumber : bankDeleteList) {
                    ChangeRecord changeRecord = new ChangeRecord();
                    changeRecord.setLegalId(legalId);
                    changeRecord.setVersion(newVersion);
                    changeRecord.setField("bank");
                    changeRecord.setOldValue(accountNumber);
                    changeRecord.setNewValue(null);
                    changeRecord.setCreateId(createId);
                    changeRecord.setCreateBy(createBy);
                    changeRecord.setCreateTime(new Date());

                    changeRecords.add(changeRecord);
                }
            }

            // 处理附件变更记录
            if (attachmentAddList != null) {
                for (String fileName : attachmentAddList) {
                    ChangeRecord changeRecord = new ChangeRecord();
                    changeRecord.setLegalId(legalId);
                    changeRecord.setVersion(newVersion);
                    changeRecord.setField("attachment");
                    changeRecord.setOldValue(null);
                    changeRecord.setNewValue(fileName);
                    changeRecord.setCreateId(createId);
                    changeRecord.setCreateBy(createBy);
                    changeRecord.setCreateTime(new Date());

                    changeRecords.add(changeRecord);
                }
            }

            if (attachmentDeleteList != null) {
                for (String fileName : attachmentDeleteList) {
                    ChangeRecord changeRecord = new ChangeRecord();
                    changeRecord.setLegalId(legalId);
                    changeRecord.setVersion(newVersion);
                    changeRecord.setField("attachment");
                    changeRecord.setOldValue(fileName);
                    changeRecord.setNewValue(null);
                    changeRecord.setCreateId(createId);
                    changeRecord.setCreateBy(createBy);
                    changeRecord.setCreateTime(new Date());

                    changeRecords.add(changeRecord);
                }
            }

            // 批量保存修改记录
            if (!changeRecords.isEmpty()) {
                changeRecordService.saveBatch(changeRecords);
                log.info("保存修改记录成功，法人ID: {}, 版本号: {}, 变更记录数: {} (字段变更: {}, 银行新增: {}, 银行删除: {}, 附件新增: {}, 附件删除: {})",
                        legalId, newVersion, changeRecords.size(), fieldChanges.size(),
                        bankAddList != null ? bankAddList.size() : 0, bankDeleteList != null ? bankDeleteList.size() : 0,
                        attachmentAddList != null ? attachmentAddList.size() : 0, attachmentDeleteList != null ? attachmentDeleteList.size() : 0);
            }
        }
    }

    private Map<String, List<String>> compareBanks(LegalEntityDetailEntity entity, UpdateLegalInfoDTO reqDTO) {
        ArrayList<String> addList = new ArrayList<>();
        ArrayList<String> deleteList = new ArrayList<>();
        HashMap<String, List<String>> result = new HashMap<>();

        // 获取数据库中的银行账号
        List<String> oldAccountNumbers = new ArrayList<>();
        if (StringUtils.hasText(entity.getBankInfoIds())) {
            List<Integer> oldBankIds = Arrays.stream(entity.getBankInfoIds().split(","))
                    .map(Integer::parseInt)
                    .toList();
            oldAccountNumbers = bankInfoMapper.selectByIds(oldBankIds)
                    .stream()
                    .map(BankInfoEntity::getAccountNumber)
                    .toList();
        }

        // 获取新的银行账号
        List<String> newAccountNumbers = new ArrayList<>();
        if (reqDTO.getBankInfoList() != null && !reqDTO.getBankInfoList().isEmpty()) {
            newAccountNumbers = reqDTO.getBankInfoList()
                    .stream()
                    .map(BankInfoDTO::getAccountNumber)
                    .filter(Objects::nonNull)
                    .toList();
        }

        // 找出删除的账号：数据库中有，现在没有
        for (String oldAccount : oldAccountNumbers) {
            if (!newAccountNumbers.contains(oldAccount)) {
                deleteList.add(oldAccount);
            }
        }

        // 找出新增的账号：现在有，数据库中没有
        for (String newAccount : newAccountNumbers) {
            if (!oldAccountNumbers.contains(newAccount)) {
                addList.add(newAccount);
            }
        }

        // 将结果放入Map
        result.put("addList", addList);
        result.put("deleteList", deleteList);

        return result;
    }

    private Map<String, List<String>> compareAttachments(LegalEntityDetailEntity entity, UpdateLegalInfoDTO reqDTO) {
        ArrayList<String> addList = new ArrayList<>();
        ArrayList<String> deleteList = new ArrayList<>();
        HashMap<String, List<String>> result = new HashMap<>();

        // 获取数据库中的附件文件名
        List<String> oldAttachmentNames = new ArrayList<>();
        if (StringUtils.hasText(entity.getAttachment())) {
            List<Long> oldAttachmentIds = Arrays.stream(entity.getAttachment().split(","))
                    .map(Long::parseLong)
                    .toList();
            // 通过文件ID获取文件名
            oldAttachmentNames = fileInfoService.getFileNamesByIds(oldAttachmentIds);
        }

        // 获取新的附件文件名
        List<String> newAttachmentNames = new ArrayList<>();
        if (reqDTO.getAttachments() != null && !reqDTO.getAttachments().isEmpty()) {
            List<Long> newAttachmentIds = reqDTO.getAttachments();
            newAttachmentNames = fileInfoService.getFileNamesByIds(newAttachmentIds);
        }

        // 找出删除的附件：数据库中有，现在没有
        for (String oldAttachment : oldAttachmentNames) {
            if (!newAttachmentNames.contains(oldAttachment)) {
                deleteList.add(oldAttachment);
            }
        }

        // 找出新增的附件：现在有，数据库中没有
        for (String newAttachment : newAttachmentNames) {
            if (!oldAttachmentNames.contains(newAttachment)) {
                addList.add(newAttachment);
            }
        }

        // 将结果放入Map
        result.put("addList", addList);
        result.put("deleteList", deleteList);

        return result;
    }

    /**
     * 获取当前最大版本号
     *
     * @param legalId 法人实体ID
     * @return 当前最大版本号
     */
    private Integer getCurrentMaxVersion(Long legalId) {
        ChangeRecord changeRecord = changeRecordService.getOne(Wrappers.<ChangeRecord>lambdaQuery()
                .eq(ChangeRecord::getLegalId, legalId)
                .orderByDesc(ChangeRecord::getVersion)
                .last("LIMIT 1"));
        return changeRecord != null ? changeRecord.getVersion() : 0;
    }

    /**
     * 比较原始实体和DTO的字段差异
     *
     * @param originalEntity 原始实体
     * @param dto            更新DTO
     * @return 字段变化映射
     */
    private Map<String, Map<String, String>> compareFieldsByDto(LegalEntityDetailEntity originalEntity, UpdateLegalInfoDTO dto) {
        Map<String, Map<String, String>> fieldChanges = new HashMap<>();

        // 比较法人实体名称
        if (!Objects.equals(originalEntity.getLegalEntityName(), dto.getLegalEntityName())) {
            Map<String, String> values = new HashMap<>();
            values.put("oldValue", originalEntity.getLegalEntityName());
            values.put("newValue", dto.getLegalEntityName());
            fieldChanges.put("legalEntityName", values);
        }

        // 比较法人实体简称
        if (!Objects.equals(originalEntity.getLegalEntityAbbr(), dto.getLegalEntityAbbr())) {
            Map<String, String> values = new HashMap<>();
            values.put("oldValue", originalEntity.getLegalEntityAbbr());
            values.put("newValue", dto.getLegalEntityAbbr());
            fieldChanges.put("legalEntityAbbr", values);
        }

        // 比较注册国家
        if (!Objects.equals(originalEntity.getRegisterCountry(), dto.getRegisterCountry())) {
            Map<String, String> values = new HashMap<>();
            values.put("oldValue", originalEntity.getRegisterCountry());
            values.put("newValue", dto.getRegisterCountry());
            fieldChanges.put("registerCountry", values);
        }

        // 比较详细地址
        if (!Objects.equals(originalEntity.getDetailAddress(), dto.getDetailAddress())) {
            Map<String, String> values = new HashMap<>();
            values.put("oldValue", originalEntity.getDetailAddress());
            values.put("newValue", dto.getDetailAddress());
            fieldChanges.put("detailAddress", values);
        }

        // 比较证件类型
        if (!Objects.equals(originalEntity.getCertificateType(), dto.getCertificateType())) {
            Map<String, String> values = new HashMap<>();
            values.put("oldValue", originalEntity.getCertificateType() != null ? originalEntity.getCertificateType().toString() : null);
            values.put("newValue", dto.getCertificateType() != null ? dto.getCertificateType().toString() : null);
            fieldChanges.put("certificateType", values);
        }

        // 比较证件ID
        if (!Objects.equals(originalEntity.getCertificateId(), dto.getCertificateId())) {
            Map<String, String> values = new HashMap<>();
            values.put("oldValue", originalEntity.getCertificateId());
            values.put("newValue", dto.getCertificateId());
            fieldChanges.put("certificateId", values);
        }

        return fieldChanges;
    }

    private void validateLegalHeaders(XSSFSheet sheet) {
        Row headerRow = sheet.getRow(0); // 假设第2行是表头
        if (headerRow == null) {
            throw new RuntimeException("法人信息表缺少表头行");
        }

        String[] expectedHeaders = {"变更类型", "基础信息序号", "法人实体编码", "法人实体名称", "法人实体简称", "注册国家/地区", "详细地址","证件类型","证件ID"};
        for (int i = 0; i < expectedHeaders.length; i++) {
            Cell cell = headerRow.getCell(i);
            if (cell == null || !expectedHeaders[i].equals(cell.getStringCellValue())) {
                throw new RuntimeException("交易方信息表第" + (i+1) + "列表头不正确，期望：" + expectedHeaders[i]);
            }
        }
    }

    private void validateBankHeaders(XSSFSheet sheet) {
        Row headerRow = sheet.getRow(0);
        if (headerRow == null) {
            throw new RuntimeException("银行信息表缺少表头行");
        }

        String[] expectedHeaders = {"变更类型", "银行信息 ID", "基础信息序号", "法人实体编码", "账号", "账户名", "银行内部ID", "银行名称", "银行国家", "币种","总账科目","清算科目","账户属性描述"};
        for (int i = 0; i < expectedHeaders.length; i++) {
            Cell cell = headerRow.getCell(i);
            if (cell == null || !expectedHeaders[i].equals(cell.getStringCellValue())) {
                throw new RuntimeException("银行信息表第" + (i+1) + "列表头不正确，期望：" + expectedHeaders[i]);
            }
        }
    }

    private void bindingDepartment(Integer legalId, List<String> sysTenantKey) {
        if (CollectionUtils.isEmpty(sysTenantKey)) {
            return;
        }
        UserInfo currentUser = AuthUtil.getCurrentUser();
        if (null == currentUser || null == currentUser.getId()) {
            return;
        }
        sysTenantKey.forEach(item -> {
            LegalTenantRel entity = new LegalTenantRel();
            entity.setLegalId(legalId);
            entity.setSysTenantKey(item);
            entity.setCreateId(currentUser.getId());
            entity.setCreateBy(currentUser.getNickName());
            entity.setCreatedAt(new Date());
            entity.setDeleted(false);
            legalTenantRelMapper.insert(entity);
        });

    }

} 